Automatic and dynamic provisioning of databases

ABSTRACT

Approaches described herein may be used for provisioning of databases that requires a bulk transfer of data within a distributed computing environment, such as a grid. The approaches do not require the manual intervention of a DBA to, for example, transfer a tablespace between the file systems of operating systems. Instead, the tablespaces may be provisioned automatically and dynamically by a grid computing system whenever it determines the need to dynamically provision a database. In addition, as copies of tablespaces are provisioned, synchronization mechanisms can also be automatically provisioned to keep the tablespaces and their copies in sync.

RELATED APPLICATIONS

The present application is a divisional of U.S. application Ser. No.10/718,747, entitled Automatic And Dynamic Provisioning of Databases,filed on Nov. 21, 2003, the contents of which are incorporated byreference: (1) which application claims priority to U.S. ProvisionalApplication No. 60/495,368, entitled Computer Resource Provisioning,filed by Debashish Chatterjee, et al. on Aug. 14, 2003, the contents ofwhich are incorporated herein by reference; (2) which application claimspriority to U.S. Provisional Application No. 60/500,050, entitledAutomatic And Dynamic Provisioning Of Databases, filed by Benny Souder,et al. on Sep. 3, 2003, the contents of which are incorporated herein byreference; (3) which application claims priority to U.S. ProvisionalApplication 60/500,096, entitled Service Based Workload Management andMeasurement in a Distributed System, filed by Carol Colrain, et al. onSep. 3, 2003, the contents of which are incorporated herein byreference.

The following applications are related to the present application:

-   -   U.S. Provisional Application No. 60/410,883, entitled Oracle        Streams, filed by Alan Downing, et al. on Sep. 13, 2002, the        contents of which are incorporated herein by reference;    -   U.S. Provisional Application No. 60/400,532, entitled Utilizing        Rules in Distributed Information Sharing, filed by Edwina Lu, et        al. on Aug. 1, 2002, the contents of which are incorporated        herein by reference;    -   U.S. application Ser. No. 10/449,873, entitled Utilizing Rules        in a Distributed Information Sharing System, filed by Edwina Lu,        et al. on May 30, 2003, the contents of which are incorporated        herein by reference; and    -   U.S. application Ser. No. 10/353,381, entitled Pluggable        Tablespaces For Database Systems, filed by Juan R. Loaiza, et        al. on Jan. 28, 2003, the contents of which are incorporated        herein by reference.

FIELD OF THE INVENTION

The present invention relates to distributed database systems, and inparticular, to allocating resources in a database system to meet varyingworkload demands.

BACKGROUND OF THE INVENTION

Enterprises are looking at ways of reducing costs and increasingefficiencies of their data processing system. A typical enterprise dataprocessing system allocates individual resources for each of theenterprise's applications. Enough resources are acquired for eachapplication to handle the estimated peak load of the application. Eachapplication has a different load characteristics; some applications arebusy during the day; some others during the night; some reports are runonce a week and some others once a month. As a result, there is a lot ofresource capacity that is left unutilized. Grid computing enables theutilization or elimination of this unutilized capacity. In fact, Gridcomputing is poised to drastically change the economics of computing.

A grid is a collection of commodity computing elements that provideprocessing and some degree of shared storage; the resources of a gridare allocated dynamically to meet the computational needs and prioritiesof its clients. An example of a grid is a rack of server blades. Eachserver blade is an inclusive computer system, with processor, memory,network connections, and associated electronics on a single motherboard.Typically, server blades do not include onboard storage (other thanvolatile memory), and they share storage units (e.g. shared disks) alongwith a power supply, cooling system, and cabling within a rack.

Grid computing can dramatically lower the cost of computing, extend theavailability of computing resources, and deliver higher productivity andhigher quality. The basic idea of Grid computing is the notion ofcomputing as a utility, analogous to the electric power grid or thetelephone network. A client of the Grid does not care where its data isor where the computation is performed. All a client wants is to havecomputation done and have the information delivered to the client whenit wants.

At a high level, the central idea of Grid computing is computing as autility. A client of a Grid should not have to care where its dataresides, or what computer element processes a request. The client needonly request information or computation and have it delivered—as much asneeded and whenever needed. This is analogous to the way electricutilities work; a customer does not know where the generator is, or howthe electric grid is wired. The customer just asks for electricity andgets it. The goal is to make computing a utility—a ubiquitous commodity.Hence it has the name, the Grid.

This view of Grid computing as a utility is, of course, a client sideview. From the server side, or behind the scenes, the Grid is aboutresource allocation, information sharing, and high availability.Resource allocation ensures that all those that need or requestresources are getting what they need. Resources are not standing idlewhile requests are left unserviced. Information sharing makes sure thatthe information clients and applications need is available where andwhen it is needed. High availability ensures that all the data andcomputation must always be there—just as a utility company must alwaysprovide electric power.

Grid Computing for Databases

One area of computer technology that can benefit from Grid computing isdatabase technology. A grid can support multiple databases anddynamically allocate resources as needed to support the load on eachdatabase. As the load for a database increases, more resources areallocated for that database. For example, on an enterprise grid, adatabase is being serviced by one database server running on one serverblade on the grid. The number of users requesting data from a databaseincreases. In response to this increase in the demand for the database,another database server is provisioned on one or more other serverblades.

Provisioning for Database Grid

The term provisioning refers to providing and configuring thecomputational resources and data needed to provide a service. Withrespect to database servers, provisioning includes configuring a serverblade to run the database server and configuring the database server tomanage a database. With respect to databases, provisioning includesconfiguring a database server to manage access to the database.

The process of provisioning data or a database is referred to herein asdata provisioning. Provisioning a database in a grid may require cloningall or part of the database, and then provisioning a new database serverto manage the clone or incorporating the clone into another databasealready being managed by an already running database server.

Data provisioning of a database can involve the bulk transfer of databetween file systems and/or databases. Unfortunately, techniques forbulk transfer of data that are used for database provisioning entailmanual intervention and therefore cannot be used to effectivelyprovision data automatically and dynamically as is required for gridcomputing.

An example of an approach for data provisioning that uses a techniquefor bulk transfer of data is the transportable tablespace approach. Atablespace is a collection of storage containers (e.g. files) used tostore data for database objects (e.g. relational tables). Under thisapproach, tablespaces are exported from a “source database” and importedinto a “target database”. This capability allows the files of atablespace to be copied using operating system utilities for copyingfiles, which run much faster than the other techniques for bulk transferof data between database. Such other techniques involve executingqueries and insert statements.

To transport a tablespace, a human database administrator (“DBA”)performs manual steps. First, the tablespace must be imported into thetarget tablespace by attaching the tablespace. With respect to atablespace, database, and database server, the term “attach” refers toconfiguring a database and/or database server so that the databaseobjects in the tablespace are incorporated within the database and thetablespace is used to store data for the database. Configuring adatabase to attach a tablespace involves modifying the database metadataso that it defines the tablespace and database objects as part of thedatabase. The database metadata may be altered using a variety oftechniques involving manual steps performed by a DBA. The DBA can runutilities available on the source database system that may be executedto export the metadata into a “metadata dump file”, and run utilities onthe target database system to construct metadata from the metadata dumpfile. Alternately, metadata can be included with the data beingtransported in the tablespace, and the target database would reconstructthe metadata from the metadata included in the tablespace. The DBA canalso manually reconstruct the metadata on the target database system.

A tablespace may be transported to a database by creating a separatecopy of the tablespace from the original source database and attachingit to the target database. While the copy is being made, operations onthe tablespace should be restricted to read-only operations. The DBAsends commands to instruct a database server managing the database torestrict database operations performed on the tablespace to read-onlyoperations. Once the copy is complete, the DBA may send commands toinstruct the database server that modification operations can beperformed.

The term “copy,” as used herein, refers to both the source data and aduplicate of the source data. For example, a copy of a source file maybe the source file itself, or another file that is a duplicate that canbe generated using, for example, readily available copy utilities, suchas operating system utilities for creating copies of data files.

The copy of a tablespace transported may also be detached from adatabase. With respect to particular tablespace and database anddatabase server, the term detach refers to configuring a database and/ordatabase server so that a tablespace is no longer used to store data forthe database. Configuring a database to detach a tablespace includesaltering database metadata in the source database system, by, forexample, removing metadata defining the tablespace as part of the sourcedatabase system, or setting a flag to indicate that the tablespace is nolonger used. This step is performed by the DBA by running utilities orby manually editing the source database metadata.

The tablespaces for the source database are stored in a “sourcedirectory” of a file system and the tablespaces of the target databasesystem are stored in a “target directory” of a file system. The sourceand target directories may be within the same file system or a differentfile system on a different computer system. In any case, the DBA needsto transfer the tablespace using operating system utilities. Thisrequires that the DBA have an operating system account on the computersystem of the target directory. The DBA logs onto the computer systemand runs a utility to transfer the tables from the source directory tothe target directory. If the target directory is in another file systemof another computer system, the DBA can use FTP to transfer the file(i.e. use a utility that follows the File Transfer Protocol). To useFTP, the DBA needs an operating system account on the computer system ofthe target directory to log onto the computer system and transfer thetablespace files.

As demonstrated above, conventional bulk transfer of data techniques forprovisioning database require manual intervention on the part of a humanDBA. Because Grid computing requires that data provisioning be performedautomatically and dynamically, database provisioning that requires thebulk transfer of databases is not amenable for Grid computing. Clearly,there is need for automated bulk transferring of databases that issuitable for dynamic data provisioning within a grid.

The approaches described in this section are approaches that could bepursued, but not necessarily approaches that have been previouslyconceived or pursued. Therefore, unless otherwise indicated, it shouldnot be assumed that any of the approaches described in this sectionqualify as prior art merely by virtue of their inclusion in thissection.

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention is illustrated by way of example, and not by wayof limitation, in the figures of the accompanying drawings and in whichlike reference numerals refer to similar elements and in which:

FIG. 1 is a block diagram depicting a distributed database system uponwhich an embodiment of the present invention may be implemented.

FIG. 2 is a block diagram of procedure used to automatically anddynamically provision tablespaces according to an embodiment of thepresent invention.

FIG. 3 is a flow chart depicting a process for automatically anddynamically provisioning tablespaces according to an embodiment of thepresent invention.

FIG. 4 is a block diagram depicting a mechanism for synchronizingtablespaces that may be automatically and dynamically provisionedaccording to an embodiment of the present invention.

FIG. 5 is a flow chart for automatically and dynamically provisioning atablespace and tablespace synchronization mechanism according to anembodiment of the present invention.

FIG. 6 is a block diagram depicting a computer system that may be usedto implement an embodiment of the present invention.

DETAILED DESCRIPTION OF THE INVENTION

A method and apparatus for automatic and dynamic provisioning ofdatabases is described. In the following description, for the purposesof explanation, numerous specific details are set forth in order toprovide a thorough understanding of the present invention. It will beapparent, however, that the present invention may be practiced withoutthese specific details. In other instances, well-known structures anddevices are shown in block diagram form in order to avoid unnecessarilyobscuring the present invention.

Approaches described herein may be used for provisioning of databasesthat requires a bulk transfer of data within in a distributed computingenvironment, such as a grid. The approaches do not require the manualintervention of a DBA to, for example, transfer a tablespace between thefile systems of operating systems. Instead, the tablespaces may beprovisioned automatically and dynamically by a grid computing systemwhenever it determines the need to dynamically provision a database. Inaddition, as copies of tablespaces are provisioned, synchronizationmechanisms can also be automatically provisioned to keep the tablespacesand their copies in sync.

Exemplary Distributed Database System

FIG. 1 shows a distributed database system 101 that may be used toimplement an embodiment of the present invention. Distributed databasesystem 101 includes database server 112 and database server 122.Database server 112 manages access to database 114 and database server122 manages access to database 124. A database server, such as databaseservers 112 and 122, is a combination of a set of integrated softwarecomponents and an allocation of computational resources, such as memoryand processes for executing the set of integrated software components ona processor, where the combination of software and computationalresources are used for managing a database. Among other functions ofdatabase management, a database server governs and facilitates access toa database, processing requests by database clients to access thedatabase. The database clients of a database server may include otherdatabase servers. Database 110 is a collection of database objects.Database objects include any form of structured data. Structured data isdata structured according to a metadata description defining thestructure. Structured data includes relational tables, object tables,object-relational tables, and bodies of data structured according to theExtensible Markup Language (“XML”), such as XML documents.

Tablespaces 119 and 129 are “online” because each of them is defined asbeing part of a database. Offline tablespaces, such as offlinetablespaces 180, are tablespaces that are detached, that is, not definedas being part of a database. The approaches described herein can be usedto provision copies of online tablespaces and offline tablespaces.

Database 114 is referred to as a local database with respect to databaseserver 112 because database server 112 does not require another databaseserver to access database 114 on database server 112's behalf. However,to access database 124, database server 112 must request access fromdatabase server 122, which accesses the data on behalf of databaseserver 112. Therefore, database 124 is not a local database of databaseserver 112.

As mentioned before, a tablespace is a collection of storage containers.According to an embodiment of the present invention, the storagecontainers are files defined and managed by an operating system. Anoperating system is software that controls and manages a computerelement's resources. Other software that is executed on the computerelement, herein “applications”, are executed by processes that operateunder the control of the operating system. The software components of adatabase server are an example of an application. Access by processesexecuting the applications to the resources is also controlled by theoperating system. Applications access the resources by calling operatingsystem routines and utilities.

Files defined and managed by an operating system are stored inpersistent storage, such as disk storage, and organized as a hierarchyof directories that contain files and other directories. A file systemdirectly controlled by an operating system on a computer element isreferred to as a local file system with respect to the operating system.The files within the file system are referred to as local files withrespect to the operating system. A file system is directly controlled bya particular operating system if access to the file system does notrequire another operating system to access the file system on theparticular operating system's behalf. Thus, a file system in a shareddisk system may be controlled by more than one operating system.

An operating system on a computing element that controls an applicationor a process is referred to herein as a local operating system withrespect to the application or process. Likewise, the application orprocess is referred to as a local application or local process withrespect to the operating system.

Processes executing under the control of an operating system areassociated with an operating system account established by operatingsystem functions responsible for registering accounts. Initiating aprocess for a user requires that the user logon under an account usingan operating system function for logging on users.

While an operating system controls the resources of a computing element,the control can be shared with applications, which share control jointlywith and subordinate to an operating system. For example, databaseserver 112 is comprised of multiple “database server processes”associated with an operating system account. Processes associated withthis operating system account, which include the multiple databaseserver processes, are granted exclusive read and write privileges by theoperating system of computer element 110 to directories in the localfile system of the operating system. Access by the database serverprocesses to data in the directories is managed by database server 112.Processes associated with other operating system accounts may not accessthese directories. In this way, database server 112 shares joint andsubordinate control over these directories and the files within them.

An operating system provides various functions and utilities that allowresources to be managed and handled in a certain way. For example, theoperating system of computer element 110 can treat disk drives as aRedundant Array of Independent (or Inexpensive) Disks. Likewise, adatabase server can provide similar capabilities.

Database transport procedures 150 include routines that may be invokedby a database server to perform a specific set of steps needed toprovision a tablespace. A procedure may perform some or all the stepsneeded to provision a tablespace, including steps conventionally andmanually performed by a DBA. The procedures may be invoked toautomatically and dynamically provision a database.

Data Transport Mechanisms

Database servers 112 and 122 transmit data between each other usingvarious data transport mechanisms (“transport mechanisms”). Thesetransport mechanisms include Database Server to Database Server filetransport mechanism 130 (“DB file transport mechanism”) and rule-basedmessaging system 140. For purposes of exposition, DB file transportmechanism 130 and rule-based messaging system 140 are depicted in FIG. 1using blocks that are separate and distinct from those used to representdatabase servers 112 and 122. However, these mechanisms may includesoftware components that are integrated within either database server112 and 122 and that participate to transmit data along an interconnect(e.g. network, bus) between the database servers. In fact, thesetransport mechanisms may use queues and tables within databases 114 and124, or files under the control of database servers 112 and 122, and mayuse procedures from database transport procedures 150.

DB file transport mechanism 130 is a transport mechanism dedicated totransferring files between database servers 112 and 122. The files maybe transported from one or more directories in a local file system ofdatabase server 112 to one or more directories of a local file system ofdatabase server 122. A local file system of a database server is a localfile system of an operating system that controls the database server.Thus, a local file system of database server 112 is the local filesystem of the operating system of computer element 110. According to anembodiment of the present invention, the files are transported using aprotocol similar to FTP.

DB file transport mechanism 130 is distinguishable from conventionalfile transport mechanisms as follows. It is a database server componentthat (1) only transports data in the form of files, and (2) transports afile between database servers, which either retrieve the file from alocal file system and, upon receipt of the file, stores it in a localfile system, and (3) transports files in this way in response to arequest to transport them, where the request designates the specificfiles to transport and the location (e.g. directory) of the source andthe destinations. The files transported maybe binary files or textfiles. DB file transport mechanism 130 also performs character-setconversion, converting the character-set format of the file transported.

While clearly transport mechanisms have transported files between filesystem directories, such mechanisms did not comprise software componentsthat are integrated components of a database server. Furthermore,database servers 112 and 122, unlike conventional database servers, areconfigured to recognize commands that specify files to be transportedbetween directories. Such commands can be entered by a user via acommand line interface that also accepts queries that conform to adatabase language, such as SQL. The commands that specify files totransport specify a file name, including a directory path of the file'slocation, and a file name and directory path to which to transport thefile.

Rule-Based Messaging System

Rule-based messaging system 140 transmits messages between databaseserver 112 and database server 122. The message contains informationabout events, events such as the creation or modification of data.Messages are used to propagate events that occur at one database serverto another database server. The other database server may then transmitthe message to yet another database server.

A common use of a messaging system is to replicate data. DML changesmade to a database object at a database server are propagated to anotherdatabase server that maintains replicas of at least a portion of thedatabase object. Rule-based messaging system 140 may be used totransport a wide range of types of data, including files.

The message flow needed from one messaging system to another messagingsystem may differ. Various types of messaging systems provide theability for users to configure the message flow between nodes in anetwork. One type of messaging system is a rule-based messaging system,such as rule-based messaging system 140, which allows a user to specifyrules that govern the flow of messages.

A rule specifies a condition and an action to perform if the conditionis met. In general, rules comply with a rules language, which is like acomputer language. Messaging systems that use rules expose informationabout events through variables or attributes that can be referenced bythe rules. The condition in a rule may be expressed using booleanexpressions that reference the variables and attributes. The rules maybe used to select which events for which messages are sent to othernodes, and what to do with a message received from another node.

Database Transport Procedures

FIG. 2 depicts database transport procedures 150 in greater detailaccording to an embodiment of the present invention. Database transportprocedures 150 include file transport procedures 210 and transportabletablespace procedures 220. File transport procedures 210 includeroutines related to transferring files between database servers whiletablespace transport procedures 220 include procedures related totransporting tablespaces. These procedures may be invoked by a databaseserver or user through a user interface, such as a command lineinterface. An invocation of a database server may specify one or moreparameters and may return one or more values (e.g. function call valueor parameter return values). The parameters are used to specify, forexample, what tablespace to detach or which file to transport.

According to an embodiment of the present invention, database transportprocedures 150 are written in a combination of C and PL/SQL™, PL/SQL isa procedural database language available from Oracle™ Corporation.However, the present invention is not limited to database transportprocedures that are written in a particular computer language.

File transport procedures 210 include procedures get 212, put 214, andcopy 216, as follows.

Get 212 This procedure causes a “destination” database server to requestfrom a “source” database server a file local to the source databaseserver. The source database server transmits the requested file to thedestination database server. The request and the file may be transportedusing DB file transport mechanism 130.

Put 214 This procedure causes a source database server to contact adestination database server to create, in the local file system of thedestination database server, a copy of a file from the local file systemof the source database server. The source database server transmits therequested file to the destination database server. The file may betransported using DB file transport mechanism 130. This procedure may beused by rule-base messaging system 140 to propagate a file via DB filetransport mechanism 130.

Copy 216 This procedure causes a database server to make a local copy ofthe file to the local file system.

Database Transport Procedures 150 include detach 222, clone 224, attach226, pull 228, and push 229, as follows.

Detach 222 This procedure causes a database server to make a set oftablespaces read-only, to detach the tablespaces from the database, andreturn the names of the files in the tablespace (“tablespace files”). Adatabase server makes a set of tablespaces read-only by only honoringand processing requests to read data from the tablespaces and preventingDML changes to data in the tablespace. Tablespace metadata for the setof tablespaces is exported to a separate file, referred to as a“metadata dump file.”

The tablespace files and the metadata dump file together form a“transportable tablespace package”. A transportable tablespace packagecan be transported using file transport procedures 210. A transportabletablespace package detached in this way can also be “re-attached” to adatabase from which it was detached.

Clone 224 This procedure copies tablespaces without detaching them.Specifically, the procedure causes a database server to make a set oftablespaces read-only, to copy their tablespace files to another set oftablespace files that may be specified by a parameter, and then makingthe set of tablespaces read-write (if read-write before invoking theprocedure). The procedure also exports metadata for the set oftablespaces to a metadata dump file. The procedure returns the names ofthe new tablespace files and the metadata dump file.

Attach 226 This procedure causes a database server to attach a set oftablespaces in a transportable set package.

Pull 228 This procedure causes a database server to copy a set oftablespaces from a remote database (one that is not local to thedatabase server) of a remote database server and attach the tablespaceinto a local database of the database server. Thus, the procedurecompletely provisions a tablespace. FIG. 3 illustrates this procedure.

Referring to FIG. 3, at step 310 the tablespaces at the remote serverare made read-only. At step 320, the database server gets metadatadescribing the tables using metadata import/export utilities. Suchutilities allow clients and database servers to connect to anotherdatabase server and get metadata describing database objects, includingmetadata describing tables and tablespaces and database objects withintablespaces. At step 330, the database server uses get 212 to get a copyof the tablespaces and store them in the local file system. At step 340,the tablespaces are imported into the local database by attaching them,using the tablespace metadata obtained at step 320. At step 350, thetablespaces at the remote server are made read-write.

Push 229 This procedure causes a source database server to copy a set oftablespaces and import them into a local database of a remote databaseserver. This procedure may be implemented by sending a message viarules-based messaging system 140 to the remote database server, themessage specifying the tablespaces to “pull”. The remote database serverthen invokes pull 228 to provision the tablespaces in the localdatabase. Another way to implement this procedure is for a sourcedatabase server to use put 214 to transport a copy of the tablespace toa remote database, to invoke metadata import/export utilities totransmit the tablespace metadata describing the tablespaces to theremote database server, and instruct the remote database server toattach the copies using the tablespace metadata.

Automatic Provisioning of Tablespace Synchronization Mechanisms

Once a copy of a tablespace is provisioned into another database,rule-based messaging system 140 may be used to keep the tablespace andits copy synchronized. To do so, various components of a rule-basedmessaging system 140 must be provisioned.

FIG. 4 is a block diagram illustrating components provisioned tosynchronize a tablespace and its provisioned copy. FIG. 5 is a flowchartdepicting a process for automatically provisioning a tablespace copy andrule-based messaging system components that are needed to synchronizethe tablespace copy with the tablespace. These components and processesare illustrated using distributed database system 101.

FIG. 4 shows a rule-based messaging system 140 in greater detail,according to an embodiment of the present invention. Referring to FIG.4, it shows that database server 112 includes a capture process 413,which captures events (e.g. DML changes and DDL changes) recorded bydatabase server 112 in change log 417. Change log 417 contains redoand/or undo records. Capture process 413 queues messages reflectingthose events into message queue 418. Propagate process 414 propagatesmessages from message queue 418 to message queue 422. Message queue 422is a staging area for messages to be applied by database server 122 todatabase 124.

Rules engines 451 and 453 execute rules 452 and 454, respectively, inresponse to requests from clients to evaluate rule sets in rules 452 or454, and then returns the evaluation results of evaluating the rule setsto the clients. Clients of rules engine 451 include capture process 413and propagate process 414. Clients of rules engine 453 include applyprocess 423. Capture process 413, propagate process 414, and applyprocess 423 use the results of rules evaluation provided by rules engine451 and 453 to determine how to process events and messages. This isaccomplished by transmitting a request to execute rule sets to a rulesengine. In response to such a request, rules engines 451 and 453 executeone or more of the rule sets. Rules-based messaging system are describedin greater detail in Utilizing Rules in Distributed Information Sharingand Utilizing Rules in a Distributed Information Sharing System.

The process depicted in FIG. 5 is illustrated by using it to provision acopy of tablespace 419 (FIG. 4) into database 124 as tablespace 419′.Database 114 is referred to as the source database because it containsthe tablespace copied and database 124 as the destination databasebecause it is the database to which the copy is attached. Databaseservers 112 and 122 are referred to as the source database server anddestination database server, respectively, because they are the localdatabase servers of source database 114 and destination database 124,respectively.

Referring to FIG. 5, at step 510, source database server 112 isconfigured to log DML changes to tablespace 419 (i.e. changes todatabase objects stored in tablespace 419), to run capture process 413to capture changes to tablespace 419, and to run propagate process 414to propagate changes to tablespace 419 to destination database server122.

At step 520, rules 452 are configured to cause capture process 413 tocapture changes to tablespace 419 and to propagate the changes todestination database server 122 and message queue 422.

At step 530, a clone of tablespace 419, tablespace 419′, is provisionedat destination database server 122 by invoking pull procedure 228.

At step 540, destination database server 122 is configured to run anapply process to apply changes from message queue 422.

At step 550, rules 454 are configured to cause apply process 423 toapply changes to tablespace 419 to tablespace 419′ (i.e. to applychanges to database objects within tablespace 419 to database objectswithin 419′).

So that the process depicted in FIG. 4 may be used to automatically anddynamically provision a tablespace, the process can be implemented as aprocedure that is invoked to provision a tablespace and rule-basedmessaging components to a particular database or database server.Alternately, a script can be generated and later executed to provisionthe tablespace and/or the synchronization mechanism.

The process depicted in FIG. 4 provisions a synchronization mechanismthat is unilateral, that is, changes to a tablespace are only propagatedfrom one tablespace to another and not vice versa. However, anembodiment of the present invention is not so limited. Bilateralsynchronization mechanisms may also be automatically provisioned. Forexample, the rules and message flow processes may be configured topropagate changes from tablespace 419′ to tablespace 419 by configuringcapture, propagate, and apply processes, and rules in a manner similarto that described to propagate changes from tablespace 419 to tablespace419′.

Additional Features of Embodiments of the Invention

While the approaches to provisioning tablespaces have been illustratedby provisioning online tablespaces, the present invention is not solimited. The approaches described herein may be used to automaticallyand dynamically provision copies of offline tablespaces.

For example, an offline tablespace 180 may contain information aboutquarterly financial results. The data in the tablespace is onlyprocessed and reported quarterly for a brief period of time eachquarter. Rather than keeping the tablespace online where it occupiesresources (e.g. storage capacity), the information can be kept offlineand provisioned and “unprovisioned” quarterly, freeing up resources andallowing the resources to be used for other services.

Although the approaches described herein provision data in the form oftablespaces, the present invention is not limited to provisioning datathat is identified by tablespace. The present invention may be used toautomatically provision a list of tables, schemas and databases, and asynchronization mechanism needed to keep them in sync, both bilaterallyand unilaterally. Provisioning lists of tables, schemas, or databasesgives customers more flexibility and a logical way to automaticallyprovision data and keep it synchronized.

Synchronization mechanisms can be provisioned within distributeddatabase systems that fall into numerous kinds of topologies. A networkof database servers interlinked by messaging systems may be representedby directed graphs of nodes. Edges join the nodes, each edgerepresenting a flow of messages from a “source” node to another adjacent“destination” node. For a given node, multiple edges can emanate fromthe node or terminate at the node. In addition, there can be cycles,representing messages flowing from a source node back to the source nodealong a path that may include one or more other nodes. Data and/orsynchronization mechanisms can be automatically provisioned intopologies that can be represented by various types of directed graphs,such as an acyclic graph or a completely connected graph, where changesbetween the adjacent nodes are bilaterally synchronized.

Nor are the approaches for automatically and dynamically provisioningdata limited to provisioning database data. The approaches may be usedto provision software and computer code, such as PL/SQL packages.Database servers provide code import/export utilities for exporting codeto “code dump files”, and for importing code from code dump files. Toautomatically provision code, for example, a database server couldinvoke a code import/export utility to export the code from a remotedatabase server and store the code in a dump file in the local filesystem, and then invoke the code import/export utility to import thecode into the local database or code repository. The automaticprovisioning of code could be used for remote job scheduling on a remotedatabase server. A database server could delegate a job or task to aremote database server and provide the needed code and/or data toperform the job or task by automatically provisioning the code and data.

Use of Other File-Based Database Provisioning Approaches

Embodiments of the present invention have been illustrated usingtransportable tablespaces as an approach for instantiating a database,that is making a copy of a database and provisioning it. However, thepresent invention is not so limited. Embodiments of the presentinvention may use other techniques for this purpose.

For example, a database may be instantiated using the command generationapproach. Under the command generation approach, a database servercreates a file that contains a description of database objects (i.e.metadata) and database language commands that insert data into thedatabase objects. Such a file is referred to herein as an import/exportfile. The import/export file may be created using, for example, exportutilities designed to export database data for selected database objectsin the database by creating metadata that describes the database objectsand SQL insert commands that insert rows in the database object. Toprovision the database data, a database server executes import utilitiesdesigned to import the data by reading the description of the databaseobjects, ensuring that the database objects are defined in the targetdatabase (defining them if necessary), and then executing the SQL insertcommands. The execution of the SQL insert commands inserts the datarow-by-row into the target database, which requires more work thanattaching a tablespace when inserting greater than a threshold amount ofdata.

This approach may be advantageous for provisioning a target databaseunder various circumstances. One is when the target database alreadydefines the database objects for the data to provision and the overheadof creating a definition of the database objects is therefore notincurred, and the amount of data to provision is small and the overheadof row-by-row insertion is therefore not significant.

Another circumstance is when, under the tablespace approach, a set ofdatabase objects to instantiate cannot be instantiated without having toinstantiate other database objects. A tablespace is used to store datafor a particular set of database objects. In some implementations oftablespaces, when using the tablespace to instantiate database objects,all the database objects in the set must be instantiated. However, underthe command generation approach, it is possible to create import/exportfiles for only a selected subset of the database objects in the set oreven from other tablespaces, enabling the ability to instantiate onlythe selected database objects no matter which tablespace is used tostore their data. Thus, the command generation approach can provide moreflexibility in forming combinations of database objects to instantiate.

To instantiate a copy of the database dynamically, a database serverruns export utilities to create an import/export file, which is thentransported between database servers using a database server filetransport mechanism or messaging system, such as database server filetransport mechanism 130 or rule based messaging system 140.Alternatively, import/export files may be stored as part of a library,to be transported and provisioned sometime later when needed.

Another approach to instantiating a database is the recovery managerapproach. The recovery manager approach uses the capabilities of arecovery manager to instantiate a database. A recovery manager is usedto create backup files of all database files (e.g. tablespace files) andrestore database files from the backup files. Restoring the databasefiles instantiates a copy of a database at the time of creation of thebackup files. If changes to a database are archived in an archive log,the archive log may be used to restore the changes. In fact, a databasecan be restored to any particular point in time covered by an archivelog.

A source database can be instantiated by using a recovery manager tocreate the backup files and using the backups and a recovery manager tocreate a target database at another location. If the backups are storedfor a period time before creating the target database, and during theperiod time the source database changes, an archive log of the sourcedatabase may be used to make the target database current or consistentwith the source database at some particular point in time. In fact, itmay be more efficient to instantiate multiple instances at differenttimes from one set of backup files by using the archive log of thesource database to update the multiple instances to make them consistentwith a given point of time.

Hardware Overview

FIG. 6 is a block diagram that illustrates a computer system 600 uponwhich an embodiment of the invention may be implemented. Computer system600 includes a bus 602 or other communication mechanism forcommunicating information, and a processor 604 coupled with bus 602 forprocessing information. Computer system 600 also includes a main memory606, such as a random access memory (RAM) or other dynamic storagedevice, coupled to bus 602 for storing information and instructions tobe executed by processor 604. Main memory 606 also may be used forstoring temporary variables or other intermediate information duringexecution of instructions to be executed by processor 604. Computersystem 600 further includes a read only memory (ROM) 608 or other staticstorage device coupled to bus 602 for storing static information andinstructions for processor 604. A storage device 610, such as a magneticdisk or optical disk, is provided and coupled to bus 602 for storinginformation and instructions.

Computer system 600 may be coupled via bus 602 to a display 612, such asa cathode ray tube (CRT), for displaying information to a computer user.An input device 614, including alphanumeric and other keys, is coupledto bus 602 for communicating information and command selections toprocessor 604. Another type of user input device is cursor control 616,such as a mouse, a trackball, or cursor direction keys for communicatingdirection information and command selections to processor 604 and forcontrolling cursor movement on display 612. This input device typicallyhas two degrees of freedom in two axes, a first axis (e.g., x) and asecond axis (e.g., y), that allows the device to specify positions in aplane.

The invention is related to the use of computer system 600 forimplementing the techniques described herein. According to oneembodiment of the invention, those techniques are performed by computersystem 600 in response to processor 604 executing one or more sequencesof one or more instructions contained in main memory 606. Suchinstructions may be read into main memory 606 from anothercomputer-readable medium, such as storage device 610. Execution of thesequences of instructions contained in main memory 606 causes processor604 to perform the process steps described herein. In alternativeembodiments, hard-wired circuitry may be used in place of or incombination with software instructions to implement the invention. Thus,embodiments of the invention are not limited to any specific combinationof hardware circuitry and software.

The term “computer-readable medium” as used herein refers to any mediumthat participates in providing instructions to processor 604 forexecution. Such a medium may take many forms, including but not limitedto, non-volatile media, volatile media, and transmission media.Non-volatile media includes, for example, optical or magnetic disks,such as storage device 610. Volatile media includes dynamic memory, suchas main memory 606. Transmission media includes coaxial cables, copperwire and fiber optics, including the wires that comprise bus 602.Transmission media can also take the form of acoustic or light waves,such as those generated during radio-wave and infra-red datacommunications.

Common forms of computer-readable media include, for example, a floppydisk, a flexible disk, hard disk, magnetic tape, or any other magneticmedium, a CD-ROM, any other optical medium, punchcards, papertape, anyother physical medium with patterns of holes, a RAM, a PROM, and EPROM,a FLASH-EPROM, any other memory chip or cartridge, a carrier wave asdescribed hereinafter, or any other medium from which a computer canread.

Various forms of computer readable media may be involved in carrying oneor more sequences of one or more instructions to processor 604 forexecution. For example, the instructions may initially be carried on amagnetic disk of a remote computer. The remote computer can load theinstructions into its dynamic memory and send the instructions over atelephone line using a modem. A modem local to computer system 600 canreceive the data on the telephone line and use an infra-red transmitterto convert the data to an infra-red signal. An infra-red detector canreceive the data carried in the infra-red signal and appropriatecircuitry can place the data on bus 602. Bus 602 carries the data tomain memory 606, from which processor 604 retrieves and executes theinstructions. The instructions received by main memory 606 mayoptionally be stored on storage device 610 either before or afterexecution by processor 604.

Computer system 600 also includes a communication interface 618 coupledto bus 602. Communication interface 618 provides a two-way datacommunication coupling to a network link 620 that is connected to alocal network 622. For example, communication interface 618 may be anintegrated services digital network (ISDN) card or a modem to provide adata communication connection to a corresponding type of telephone line.As another example, communication interface 618 may be a local areanetwork (LAN) card to provide a data communication connection to acompatible LAN. Wireless links may also be implemented. In any suchimplementation, communication interface 618 sends and receiveselectrical, electromagnetic or optical signals that carry digital datastreams representing various types of information.

Network link 620 typically provides data communication through one ormore networks to other data devices. For example, network link 620 mayprovide a connection through local network 622 to a host computer 624 orto data equipment operated by an Internet Service Provider (ISP) 626.ISP 626 in turn provides data communication services through the worldwide packet data communication network now commonly referred to as the“Internet” 628. Local network 622 and Internet 628 both use electrical,electromagnetic or optical signals that carry digital data streams. Thesignals through the various networks and the signals on network link 620and through communication interface 618, which carry the digital data toand from computer system 600, are exemplary forms of carrier wavestransporting the information.

Computer system 600 can send messages and receive data, includingprogram code, through the network(s), network link 620 and communicationinterface 618. In the Internet example, a server 630 might transmit arequested code for an application program through Internet 628, ISP 626,local network 622 and communication interface 618.

The received code may be executed by processor 604 as it is received,and/or stored in storage device 610, or other non-volatile storage forlater execution. In this manner, computer system 600 may obtainapplication code in the form of a carrier wave.

In the foregoing specification, embodiments of the invention have beendescribed with reference to numerous specific details that may vary fromimplementation to implementation. Thus, the sole and exclusive indicatorof what is the invention, and is intended by the applicants to be theinvention, is the set of claims that issue from this application, in thespecific form in which such claims issue, including any subsequentcorrection. Any definitions expressly set forth herein for termscontained in such claims shall govern the meaning of such terms as usedin the claims. Hence, no limitation, element, property, feature,advantage or attribute that is not expressly recited in a claim shouldlimit the scope of such claim in any way. The specification and drawingsare, accordingly, to be regarded in an illustrative rather than arestrictive sense.

1. A method for a database server to provide copies of files, the methodcomprising the steps of: a first database server receiving a request tocreate a copy of a file stored in a first file system of a firstoperating system; said first database server causing the creation ofsaid copy in a particular file system of a particular operating system;and wherein said copy is a different file than said particular file. 2.The method of claim 1, wherein: the step of a first database serverreceiving a request includes the first database server receiving arequest to transport a copy of the file to said particular file system;wherein the first database server causing the creation of said copyincludes causing the transmission of the copy of said file between saidfirst database server and a second database server; and storing saidcopy in said particular file system.
 3. The method of claim 2, wherein:said first file system is local relative to said first database serverand remote relative to said second database server; said particular filesystem is local relative to said second database server and remoterelative to said first database server; and wherein the step of storingis performed by said second database server.
 4. The method of claim 2,wherein the step of causing the transmission includes causing thetransmission of the copy as a binary file via a messaging system thatpropagates messages between said first database server and said seconddatabase server.
 5. The method of claim 1, wherein: said first filesystem is local relative to said second database server and remoterelative to said first database server; said particular file system islocal relative to said first database server and remote relative to saidsecond database server; and wherein the step of storing is performed bysaid first database server.
 6. The method of claim 1, wherein said firstfile system is local relative to said first database server and saidparticular file system is local relative to said first database server.7. The method of claim 1, wherein receiving a request includes theinvocation of a routine that passes as a parameter a value identifyingthe file.
 8. The method of claim 1, wherein: receiving a requestincludes receiving a command through an interface; said database serverexecutes commands received through said interface that conform to adatabase language; and said command identifies the file.
 9. Acomputer-readable non-transitory storage medium storing one or moreinstructions for a database server to provide copies of files, whichinstructions, when executed, cause one or more processors to perform: afirst database server receiving a request to create a copy of a filestored in a first file system of a first operating system; said firstdatabase server causing the creation of said copy in a particular filesystem of a particular operating system; and wherein said copy is adifferent file than said particular file.
 10. The computer-readablenon-transitory storage medium of claim 9, wherein: the step of a firstdatabase server receiving a request includes the first database serverreceiving a request to transport a copy of the file to said particularfile system; wherein the first database server causing the creation ofsaid copy includes causing the transmission of the copy of said filebetween said first database server and a second database server; andstoring said copy in said particular file system.
 11. Thecomputer-readable non-transitory storage medium of claim 10, wherein:said first file system is local relative to said first database serverand remote relative to said second database server; said particular filesystem is local relative to said second database server and remoterelative to said first database server; and wherein the step of storingis performed by said second database server.
 12. The computer-readablenon-transitory storage medium of claim 10, wherein the step of causingthe transmission includes causing the transmission of the copy as abinary file via a messaging system that propagates messages between saidfirst database server and said second database server.
 13. Thecomputer-readable non-transitory storage medium of claim 9, wherein:said first file system is local relative to said second database serverand remote relative to said first database server; said particular filesystem is local relative to said first database server and remoterelative to said second database server; and wherein the step of storingis performed by said first database server.
 14. The computer-readablenon-transitory storage medium of claim 9, wherein said first file systemis local relative to said first database server and said particular filesystem is local relative to said first database server.
 15. Thecomputer-readable non-transitory storage medium of claim 9, whereinreceiving a request includes the invocation of a routine that passes asa parameter a value identifying the file.
 16. The computer-readablenon-transitory storage medium of claim 9, wherein: receiving a requestincludes receiving a command through an interface; said database serverexecutes commands received through said interface that conform to adatabase language; and said command identifies the file.